package com.itheima.jdbcTemplate;

import com.itheima.po.Account;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * ClassName:JdbcTemplateDemo
 * Package:com.itheima.jdbcTemplate
 * Description:jdbc的CRUD操作
 * Date:2020/2/25 23:18
 * User: snow
 */
public class JdbcTemplateDemo3 {
	public static void main(String[] args) {
		//1、获取容器
		ApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
		//2、创建对象
		JdbcTemplate jdbcTemplate = applicationContext.getBean("jdbcTemplate", JdbcTemplate.class);
		//3、执行操作
		// jdbcTemplate.execute("insert into account(name ,money) values ('order',1000)");
		// 单表
		//保存
		// jdbcTemplate.update("insert into account (name,money) values (?,?)", "king", 2000f);
		// 更新
		// jdbcTemplate.update("update account set name = ?,money = ? where id = ?","waiter",500f,7);
		// 删除
		// jdbcTemplate.update("delete from account where id = ?", 15);
		// 查询所有
		// List<Account> accounts= jdbcTemplate.query("select * from account where money > ?", new AccountRowMapper(), 500f);
	/*	List<Account> accounts= jdbcTemplate.query("select * from account where money > ?", new BeanPropertyRowMapper<Account>(Account.class), 500f);
		for (Account account : accounts) {
			System.out.println(account);
		}
		*/
		// 查询一个
		/*List<Account> accounts = jdbcTemplate.query("select * from account where id = ?", new BeanPropertyRowMapper<Account>(Account.class), 13);
		System.out.println(accounts.isEmpty() ? "没有内容" : accounts.get(0));*/
		// 查询返回一行一列（聚合函数，but don't add by group子句）
		Long count = jdbcTemplate.queryForObject("select count(*) from account where money > ?", long.class, 500f);
		System.out.println(count);
		// 修改


	}

}

/**
 * 定义 Account 的封装策略
 */
class AccountRowMapper implements RowMapper<Account> {

	/**
	 * 把结果集中的数据封装到Account中，然后由Spring把Account加到集合中
	 *
	 * @param resultSet
	 * @param i
	 * @return
	 * @throws SQLException
	 */
	public Account mapRow(ResultSet resultSet, int i) throws SQLException {
		Account account = new Account();
		account.setId(resultSet.getInt("id"));
		account.setName(resultSet.getString("name"));
		account.setMoney(resultSet.getFloat("money"));

		return account;
	}
}